﻿using System.Data;
using Dapper;
using VirtualWall.Models;
using MySql.Data.MySqlClient;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SQLite;
using System.Linq;

namespace VirtualWall
{
    public class SQLDataAccess
    {
        //log
        private static readonly log4net.ILog log = LogHelper.GetLogger();

        private static readonly string db = ConfigurationManager.AppSettings["DBSelect"];

        #region UserModel

        public static List<UserModel> LoadActiveUser()
        {
            IDbConnection cnn;

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            var output = cnn.Query<UserModel>("select UserId, UserName, LoginId, LoginPwd, Valid from user where Valid = 1;", new DynamicParameters());

            cnn.Close();

            return output.ToList();
        }

        public static void UpdateUser(UserModel user)
        {
            IDbConnection cnn;

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            cnn.Execute("UPDATE User SET UserName = @UserName, LoginId = @LoginId, LoginPwd = @LoginPwd WHERE UserId = @UserId;", user);

            cnn.Close();
        }

        #endregion

        #region RoleModel

        public static string LoadLoginRolePermission(string userId)
        {
            IDbConnection cnn;
            string sql = string.Format("select Role.RolePermission from Role left join User on Role.RoleName = User.UserRole where user.LoginId = '{0}';",userId);

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            var output = cnn.QueryFirstOrDefault<string>(sql, new DynamicParameters());

            cnn.Close();

            return output;
        }

        #endregion
        
        #region GoodsModel
        
        public static List<GoodsModel> LoadActiveGoods()
        {
            IDbConnection cnn;

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            var output = cnn.Query<GoodsModel>("select * from Goods where Valid = 1;", new DynamicParameters());

            cnn.Close();

            return output.ToList();
        }

        public static GoodsModel LoadGoods(string goodsName)
        {
            IDbConnection cnn;

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            var output = cnn.QueryFirstOrDefault<GoodsModel>("select * from Goods where Valid = 1 and Name = '" + goodsName + "';", new DynamicParameters());

            cnn.Close();

            return output;
        }

        public static GoodsModel LoadGoods(int? goodsID)
        {
            IDbConnection cnn;

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            var output = cnn.QueryFirstOrDefault<GoodsModel>("select * from Goods where Valid = 1 and Id = " + goodsID.ToString(), new DynamicParameters());

            cnn.Close();

            return output;
        }

        public static GoodsModel LoadDisabledGoods(string goodsName)
        {
            IDbConnection cnn;

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            var output = cnn.QueryFirstOrDefault<GoodsModel>("select * from Goods where Valid = 0 and Name = '" + goodsName + "';", new DynamicParameters());

            cnn.Close();

            return output;
        }

        public static void SaveGoods(GoodsModel goods)
        {
            IDbConnection cnn;

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            cnn.Execute("INSERT INTO Goods (Num,Name) VALUES (@Num, @Name);", goods);

            cnn.Close();

            log.Info("新增物资:" + goods.Name);
        }
        
        #endregion

        #region CustomerModel
        
        public static List<CustomerModel> LoadActiveCustomer()
        {
            IDbConnection cnn;

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            var output = cnn.Query<CustomerModel>("select * from Customer where Valid = 1;", new DynamicParameters());

            cnn.Close();

            return output.ToList();
        }

        public static CustomerModel LoadCustomer(string customerName)
        {
            IDbConnection cnn;

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            var output = cnn.QueryFirstOrDefault<CustomerModel>("select * from Customer where Valid = 1 and Name = '" + customerName + "';", new DynamicParameters());

            cnn.Close();

            return output;
        }

        public static CustomerModel LoadCustomer(int? customerId)
        {
            IDbConnection cnn;

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            var output = cnn.QueryFirstOrDefault<CustomerModel>("select * from Customer where Valid = 1 and Id = '" + customerId + "';", new DynamicParameters());

            cnn.Close();

            return output;
        }

        public static CustomerModel LoadDisabledCustomer(string customerName)
        {
            IDbConnection cnn;

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            var output = cnn.QueryFirstOrDefault<CustomerModel>("select * from Customer where Valid = 0 and Name = '" + customerName + "';", new DynamicParameters());

            cnn.Close();

            return output;
        }

        public static void SaveCustomer(CustomerModel customer)
        {
            IDbConnection cnn;

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            cnn.Execute("INSERT INTO Customer (Num, Name, Manager, Phone, Comment) VALUES (@Num, @Name, @Manager, @Phone, @Comment);", customer);

            cnn.Close();

            log.Info("新增客户:" + customer.Name);
        }

        #endregion

        #region ICCardModel

        public static ICCardModel LoadICCard(string cardId)
        {
            IDbConnection cnn;

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            var output = cnn.QueryFirstOrDefault<ICCardModel>("select * from ICCard where Id = '" + cardId + "';", new DynamicParameters());

            cnn.Close();

            return output;
        }

        public static ICCardModel LoadICCard(string plateNo, int i)
        {
            IDbConnection cnn;
            string sql = string.Format("select * from ICCard where CarAutoNo = (select AutoNo from Car where PlateNo = '{0}')", plateNo);
            
            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            var output = cnn.QueryFirstOrDefault<ICCardModel>(sql, new DynamicParameters());

            cnn.Close();

            return output;
        }

        public static void UpdateICCard(ICCardModel ICCard)
        {
            IDbConnection cnn;

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            cnn.Execute("UPDATE ICCard SET CustomerId = @CustomerId, GoodsId = @GoodsId, CarAutoNo = @CarAutoNo, By1 = @By1, By2 = @By2, By3 = @By3, By4 = @By4, By5 = @By5 WHERE Id = @Id;", ICCard);

            cnn.Close();
        }
        #endregion

        #region CarModel

        public static List<CarModel> LoadActiveCar()
        {
            IDbConnection cnn;

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            var output = cnn.Query<CarModel>("select * from Car where Valid = 1;", new DynamicParameters());

            cnn.Close();

            return output.ToList();
        }

        public static CarModel LoadCar(string PlateNo)
        {
            IDbConnection cnn;
            string sql = string.Format("select * from Car where Valid = 1 and PlateNo = '{0}';", PlateNo);

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            var output = cnn.QueryFirstOrDefault<CarModel>(sql, new DynamicParameters());

            cnn.Close();

            return output;
        }

        public static CarModel LoadCar(int? CarAutoNo)
        {
            IDbConnection cnn;

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            var output = cnn.QueryFirstOrDefault<CarModel>("select * from Car where Valid = 1 and AutoNo = " + CarAutoNo.ToString(), new DynamicParameters());

            cnn.Close();

            return output;
        }

        public static CarModel LoadDisabledCar(string PlateNo)
        {
            IDbConnection cnn;
            string sql = string.Format("select * from Car where Valid = 0 and PlateNo = '{0}';", PlateNo);

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            var output = cnn.QueryFirstOrDefault<CarModel>(sql, new DynamicParameters());

            cnn.Close();

            return output;
        }

        public static void SaveCar(CarModel car)
        {
            IDbConnection cnn;

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            cnn.Execute("INSERT INTO Car (PlateNo, VehicleWeight, CarOwner) VALUES (@PlateNo, @VehicleWeight, @CarOwner);", car);

            cnn.Close();

            log.Info("新增车辆:" + car.PlateNo);
        }

        #endregion
        
        #region WeighingRecordModel
        
        //查未完成的称重记录
        public static WeighingRecordModel LoadWeighingRecord(string PlateNo, bool IsFinish)
        {
            IDbConnection cnn;
            string sql = string.Format("select * from WeighingRecord where Valid = 1 and Ch = '{0}' and IsFinish = {1}", PlateNo, IsFinish);

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            var output = cnn.QueryFirstOrDefault<WeighingRecordModel>(sql, new DynamicParameters());

            cnn.Close();

            return output;
        }
        /// <summary>
        /// 获取该车辆最近的场内称重记录
        /// </summary>
        /// <param name="plateNo"></param>
        /// <returns></returns>
        public static WeighingRecordModel GetWeighingRecord(string plateNo)
        {
            IDbConnection cnn;
            string sql = string.Format("select * from WeighingRecord where Ch = '{0}' order by Bh desc limit 1;", plateNo);

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            var output = cnn.QueryFirstOrDefault<WeighingRecordModel>(sql, new DynamicParameters());

            cnn.Close();

            return output;
        }

        /// <summary>
        /// 获取该车辆最近的场内称重记录
        /// </summary>
        /// <param name="plateNo"></param>
        /// <returns></returns>
        public static WeighingRecordModel GetWeighingRecordByBh(string bh)
        {
            IDbConnection cnn;
            string sql = string.Format("select * from WeighingRecord where Bh = '{0}' order by Bh desc limit 1;", bh);

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            var output = cnn.QueryFirstOrDefault<WeighingRecordModel>(sql, new DynamicParameters());

            cnn.Close();

            return output;
        }

        public static WeighingRecordModel LoadWeighingRecord(string Bh)
        {
            IDbConnection cnn;
            string sql = string.Format("select Bh from WeighingRecord where Bh like '{0}%' order by Bh desc limit 1;", Bh);

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            var output = cnn.QueryFirstOrDefault<WeighingRecordModel>(sql, new DynamicParameters());

            cnn.Close();

            return output;
        }

        public static void SaveWeighingRecord(WeighingRecordModel WeighingRecord)
        {
            IDbConnection cnn;

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            cnn.Execute("INSERT INTO WeighingRecord (Bh, Kh, Kh2, Kh3, Ch, Wz, Mz, Mzrq, Mzsby, Pz, Pzrq, Pzsby, Jz, Jzrq, Kz, Kl, Bz, ICCard, By1, By2, By3, By4, By5, WeighName, Weigh2Name,  WeighingTimes, WeighingFormTemplate, IsFinish, Je) " +
                    "VALUES (@Bh, @Kh, @Kh2, @Kh3, @Ch, @Wz, @Mz, @Mzrq, @Mzsby, @Pz, @Pzrq, @Pzsby, @Jz, @Jzrq, @Kz, @Kl, @Bz, @ICCard, @By1, @By2, @By3, @By4, @By5, @WeighName, @Weigh2Name, @WeighingTimes, @WeighingFormTemplate, @IsFinish, @Je);", WeighingRecord);

            cnn.Close();
        }

        public static void UpdateWeighingRecord(WeighingRecordModel WeighingRecord)
        {
            IDbConnection cnn;

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            cnn.Execute("UPDATE WeighingRecord SET Kh = @Kh, Kh2 = @Kh2, Kh3 = @Kh3, Wz = @Wz, Mz = @Mz, Mzrq = @Mzrq, Mzsby = @Mzsby, Pz = @Pz, Pzrq = @Pzrq, Pzsby = @Pzsby, Jz = @Jz, Jzrq = @Jzrq, Kz = @kz, Kl = @Kl, Sz = @Sz, Bz = @Bz, ICCard = @ICCard, By1 = @By1, By2 = @By2, By3 = @By3, By4 = @By4, By5 = @By5, WeighName = @WeighName, Weigh2Name = @Weigh2Name, WeighingTimes = @WeighingTimes, WeighingFormTemplate = @WeighingFormTemplate, IsFinish = @IsFinish, Je = @Je,IsPay = @IsPay,Zflsh=@Zflsh,Zfsj=@Zfsj,Zfje=@Zfje,Zfdw=@Zfdw,Zfdzsm=@Zfdzsm WHERE Bh = @Bh;", WeighingRecord);

            cnn.Close();
        }

        public static int LoadWeighingRecordCount()
        {
            IDbConnection cnn;
            string sql = string.Format("select AutoNo from WeighingRecord;");

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            var output = cnn.Query<WeighingRecordModel>(sql, new DynamicParameters());

            cnn.Close();

            return output.Count();
        }
        public static List<WeighingRecordModel> LoadWeighingRecordCount(string plateno, string date)
        {
            IDbConnection cnn;
            string sql = $"select * from weighingrecord where Ch = '{plateno}' and IsFinish = '1' and Jzrq like '{date}%';";

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            var output = cnn.Query<WeighingRecordModel>(sql, new DynamicParameters());

            cnn.Close();

            return output.ToList();
        }
        #endregion

        #region WeighingImgModel
        public static void SaveWeighingImg(WeighingImgModel WeighingImg)
        {
            IDbConnection cnn;
            string sql = "INSERT INTO WeighingImg (WRBh, Pic1, Pic2, Pic3, Pic4, Pic5, Pic6, Video1, Video2) VALUES (@WRBh, @Pic1, @Pic2, @Pic3, @Pic4, @Pic5, @Pic6, @Video1, @Video2);";

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            cnn.Execute(sql, WeighingImg);

            cnn.Close();
        }
        #endregion

        #region SyncDataModel
        public static void SaveSyncData(SyncDataModel SyncData)
        {
            IDbConnection cnn;

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            cnn.Execute("INSERT INTO SyncData (WeighingRecordBh, SyncMode) VALUES (@WeighingRecordBh, @SyncMode);", SyncData);

            cnn.Close();
        }
        #endregion

        #region 备用字段
        public static List<string> LoadByxValue(string byName)
        {
            IDbConnection cnn;
            string sql = string.Format("select distinct {0} from WeighingRecord where valid = 1 order by {0} desc limit 1000;", byName);
                    
            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            var output = cnn.Query<string>(sql, new DynamicParameters());

            cnn.Close();

            return output.ToList();
        }

        #endregion

        #region SysLog
        public static List<SysLogModel> LoadSysLog(string keywords)
        {
            string sql = string.Format("select * from Log where Message like '%{0}%' or Date like '%{0}%' limit 1000;", keywords);
            using (IDbConnection cnn = new SQLiteConnection(LoadConnectionString("LogDB")))
            {
                var output = cnn.Query<SysLogModel>(sql, new DynamicParameters());
                return output.ToList();
            }
        }

        public static void DeleteSysLog(SysLogModel log)
        {
            string sql = string.Format("DELETE FROM Log WHERE LogId = {0};", log.LogId);

            using (IDbConnection cnn = new SQLiteConnection(LoadConnectionString("LogDB")))
            {
                cnn.Execute(sql, log);
            }
        }
        #endregion

        #region 车辆进场收费数据操作

        public static CarFeeModel LoadCarFee(string PlateNo)
        {
            IDbConnection cnn;
            string sql = string.Format("select * from CarFee where PlateNo = '{0}' and ISFinish=1;", PlateNo);

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            var output = cnn.QueryFirstOrDefault<CarFeeModel>(sql, new DynamicParameters());

            cnn.Close();

            return output;
        }
        public static CarFeeModel CarFeeNoFinish(string PlateNo)
        {
            IDbConnection cnn;
            string sql = string.Format("select * from CarFee where PlateNo = '{0}' and ISFinish=0;", PlateNo);

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            var output = cnn.QueryFirstOrDefault<CarFeeModel>(sql, new DynamicParameters());

            cnn.Close();

            return output;
        }

        public static CarFeeModel GetCarFeeByTclsh(string Tclsh)
        {
            IDbConnection cnn;
            string sql = string.Format("select * from CarFee where Tclsh = '{0}';", Tclsh);

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            var output = cnn.QueryFirstOrDefault<CarFeeModel>(sql, new DynamicParameters());

            cnn.Close();

            return output;
        }

        public static void SaveCarFee(CarFeeModel carFee)
        {
            IDbConnection cnn;

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            cnn.Execute("INSERT INTO CarFee (PlateNo, InDate) VALUES (@PlateNo, @InDate);", carFee);

            cnn.Close();
        }

        public static void UpdateCarFee(CarFeeModel carFee)
        {
            IDbConnection cnn;

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            cnn.Execute("UPDATE CarFee SET PlateNo = @PlateNo, InDate = @InDate, OutDate = @OutDate, Fee = @Fee, ISFinish = @ISFinish,IsPay = @IsPay,Zflsh=@Zflsh,Zfsj=@Zfsj,Zfje=@Zfje,Zfdw=@Zfdw,Zfdzsm=@Zfdzsm,Tclsh=@Tclsh WHERE AutoNo = @AutoNo;", carFee);

            cnn.Close();
        }

        #endregion

        #region CarLabelModel

        public static CarLabelModel LoadCarLabel(string labelNo)
        {
            IDbConnection cnn;

            if (db == "mysql")
                cnn = new MySqlConnection(LoadConnectionString("AWSMYSQL"));
            else
                cnn = new SQLiteConnection(LoadConnectionString());

            var output = cnn.QueryFirstOrDefault<CarLabelModel>("select * from CarLabel where LabelNo = '" + labelNo + "';", new DynamicParameters());

            cnn.Close();

            return output;
        }

        #endregion
        private static string LoadConnectionString(string id = "AWSDB") => ConfigurationManager.ConnectionStrings[id].ConnectionString;

        public static bool CheckConnectionStatus(string id = "AWSDB")
        {
            bool status = false;

            if (id == "AWSDB")
            {
                IDbConnection cnn = new SQLiteConnection(LoadConnectionString(id));
                try
                {
                    cnn.Open();
                    if (cnn.State == ConnectionState.Open)
                    {
                        status = true;
                        cnn.Close();
                    }
                }
                catch { }
            }
            if (id == "AWSMYSQL")
            {
                IDbConnection cnn = new MySqlConnection(LoadConnectionString(id));
                
                try
                {
                    cnn.Open();
                    if (cnn.State == ConnectionState.Open)
                    {
                        status = true;
                        cnn.Close();
                    }
                }
                catch (System.Exception e)
                {
                    log.Debug(e.Message);
                }
            }

            return status;
        }
        
    }
}
